
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;



public class createxltosql {

	public static void readxlsx(String excelFile, String sheetValue)
			throws InvalidFormatException, IOException {
		Workbook workbook = null;
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		String excelvalue = null;
		Iterator<Row> rowIterator = null;
		Iterator<Cell> cellIterator = null;
		FileInputStream fileIStream = null;
		ss xlsx = new ss();
		String tablename = sheetValue;
		fileIStream = new FileInputStream(excelFile);
		workbook = WorkbookFactory.create(fileIStream);
		sheet = workbook.getSheet(sheetValue);
		xlsx.readxlsxcolumn(sheet);
		xlsx.sheet = sheet;
		Hashtable columnshashtable = new Hashtable();
		System.out.println(excelFile.substring(0, excelFile.lastIndexOf("."))+".sql");
		PrintWriter sql=new PrintWriter(new FileWriter(excelFile.substring(0, excelFile.lastIndexOf("."))+".sql",false));
		// Get an iterator to work through the rows on the sheet
		rowIterator = sheet.iterator();
		int rowStart = 1, rowEnd = xlsx.rowcount;
		int j = rowStart, k = 1;

		// Iterate through the rows
		if (rowStart >= 1) {
			for (; k <= rowStart; k++) {
				row = rowIterator.next();
			}
		}
		while (rowIterator.hasNext()) {
			int i1 = 0;
			ArrayList<String> col = new ArrayList();
			ArrayList<String> val = new ArrayList();
			/*
			 * if(rowStart==1) { row = rowIterator.next(); }
			 */
			row = rowIterator.next();
			/* System.out.println("Processing row number: " + row.getRowNum()); */
			// Get an iterator to work through the cells on the row
			cellIterator = row.iterator();
			// Iterate through the cells
			if (rowStart < rowEnd) {
				// System.out.println("row no :"+rowStart);
				// Enumeration enumKeys = mapProperties.keys();
				String key, keyValue, excelValue = null, excelValue1 = null;
				// while(enumKeys.hasMoreElements()){
				String sqlcol = "", sqlval = "'";
				System.out.print("Insert into " + tablename + " (");
				sql.print("Insert into " + tablename + " (");
				while (cellIterator.hasNext()) {
					cell = cellIterator.next();
					cell.setCellType(1);
					excelvalue = cell.getRichStringCellValue().getString();
					// System.out.print(excelvalue+"="+xlsx.columnVector.elementAt(i1));

					val.add(excelvalue);
					col.add(xlsx.columnVector.elementAt(i1).toString());

					columnshashtable.put(xlsx.columnVector.elementAt(i1),
							excelvalue);
					i1++;
				}
				Iterator colit = col.iterator();
				Iterator valit = val.iterator();
				while (valit.hasNext()) {

					sqlcol += colit.next() + ",";
					sqlval += valit.next() + "',";

				}
				System.out.print(sqlcol.substring(0, sqlcol.lastIndexOf(",")));
				sql.print(sqlcol.substring(0, sqlcol.lastIndexOf(",")));
				
				System.out.print(") into values ( ");
				sql.print(") into values ( ");
				sql.print(sqlval.substring(0, sqlval.lastIndexOf(","))+ ");");
				sql.println();
				System.out.print(sqlval.substring(0, sqlval.lastIndexOf(","))
						+ ");");
				System.out.println();
			}
		}
		sql.close();
	}

	public static void main(String[] a) throws IOException {
		try {
			createxltosql.readxlsx(a[0], a[1]);
		} catch (InvalidFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
